﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using WebApplication1.Common;
using WebApplication1.Models;
using System.Collections.Specialized;

namespace WebApplication1.BLL
{
    public class GrowBLL
    {
        /// <summary>
        /// 保存发表的成长档案
        /// </summary>
        /// <param name="imgListTxt">图片URL集合json字符串</param>
        /// <param name="userId">用户id</param>
        /// <param name="word">说说</param>
        /// <param name="schoolId">学校ID</param>
        /// <returns></returns>
        public int AddGrowImgList(string imgListTxt, string userId, string word, string schoolId, string serverPath)
        {
            List<string> imgUrlList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<string>>(imgListTxt);
            string growId = Guid.NewGuid().ToString();//成长变化表主键
            List<object[]> sqlCmdList = new List<object[]>();
            //主表
            string sqlMain = "INSERT INTO [dbo].[T_Grow]([id],[userId],[words],[creatTime],[status],[schoolId]) VALUES(@id,@userId,@words,@creatTime,@status,@schoolId)";
            SqlParameter[] parames = new SqlParameter[]
            {
                new SqlParameter("@id",growId),
                new SqlParameter("@userId",userId),
                new SqlParameter("@words",word),
                new SqlParameter("@creatTime",DateTime.Now),
                new SqlParameter("@status","1"),
                new SqlParameter("@schoolId",schoolId)
            };
            object[] arrMain = { sqlMain, parames };
            sqlCmdList.Add(arrMain);
            //图片集合
            if (imgUrlList != null && imgUrlList.Count > 0)
            {
                string sqlImg = "INSERT INTO [dbo].[T_growImgList]([id],[masterId],[imgURL],[creatTime]) VALUES(@id,@masterId,@imgURL,@creatTime)";
                foreach (string item in imgUrlList)
                {
                    SqlParameter[] imgParame = new SqlParameter[]
                    {
                    new SqlParameter("@id",Guid.NewGuid().ToString()),
                    new SqlParameter("@masterId",growId),
                    new SqlParameter("@imgURL",item),
                    new SqlParameter("@creatTime",DateTime.Now),
                    };
                    object[] arr = { sqlImg, imgParame };
                    //生成该原图的缩略图
                    if (string.IsNullOrEmpty(item) == false)
                    {
                        string fileName = "";
                        try
                        {
                            string imgURL = item.TrimStart('/');
                            imgURL = imgURL.Replace("/", "\\");
                            string oldImgPath = serverPath + imgURL;//原始图片物理地址
                            fileName = System.IO.Path.GetFileName(imgURL);//文件名扩展名
                            string suoLuePhotoPath = serverPath + "SuoLuePhoto"; //生成的缩略图文件夹
                            if (System.IO.Directory.Exists(suoLuePhotoPath) == false)
                            {
                                System.IO.Directory.CreateDirectory(suoLuePhotoPath);
                            }
                            string sltImgPath = suoLuePhotoPath + "\\" + fileName;//缩略图物理地址
                            ImgChuLi.SendSmallImage(oldImgPath, sltImgPath, 240, 240, 75, "CUT");
                        }
                        catch (Exception ex)
                        {
                            string saveFolder = "Log\\BLL";
                            string msg = "GrowBLL/AddGrowImgList，生成缩略图出错,文件名：" + fileName + "," + ex.Message;
                            Common.LogManager.AddLog(msg, saveFolder);
                        }
                    }
                    sqlCmdList.Add(arr);
                }
            }
            int efCount = SQLHepler.ExecSqlTransacation(sqlCmdList);
            return efCount;
        }

        /// <summary>
        /// 保存发表的成长档案2
        /// </summary>
        /// <param name="imgListTxt">图片URL集合json字符串</param>
        /// <param name="userId">用户id</param>
        /// <param name="word">说说</param>
        /// <param name="schoolId">学校id</param>
        /// <param name="classId">当前学生班级</param>
        /// <param name="serverPath">web项目更目录物理地址</param>
        /// <returns></returns>
        public int AddGrowImgList2(string imgListTxt, string userId, string word, string schoolId, string classId, string serverPath)
        {
            List<string> imgUrlList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<string>>(imgListTxt);
            string growId = Guid.NewGuid().ToString();//成长变化表主键
            List<object[]> sqlCmdList = new List<object[]>();
            //主表
            string sqlMain = "INSERT INTO [dbo].[T_Grow]([id],[userId],[words],[creatTime],[status],[schoolId],[classId]) VALUES(@id,@userId,@words,@creatTime,@status,@schoolId,@classId)";
            SqlParameter[] parames = new SqlParameter[]
            {
                new SqlParameter("@id",growId),
                new SqlParameter("@userId",userId),
                new SqlParameter("@words",word),
                new SqlParameter("@creatTime",DateTime.Now),
                new SqlParameter("@status","1"),
                new SqlParameter("@schoolId",schoolId),
                new SqlParameter("@classId",classId)
            };
            object[] arrMain = { sqlMain, parames };
            sqlCmdList.Add(arrMain);
            //图片集合
            if (imgUrlList != null && imgUrlList.Count > 0)
            {
                string sqlImg = "INSERT INTO [dbo].[T_growImgList]([id],[masterId],[imgURL],[creatTime]) VALUES(@id,@masterId,@imgURL,@creatTime)";
                foreach (string item in imgUrlList)
                {
                    SqlParameter[] imgParame = new SqlParameter[]
                    {
                    new SqlParameter("@id",Guid.NewGuid().ToString()),
                    new SqlParameter("@masterId",growId),
                    new SqlParameter("@imgURL",item),
                    new SqlParameter("@creatTime",DateTime.Now),
                    };
                    object[] arr = { sqlImg, imgParame };
                    //生成该原图的缩略图
                    if (string.IsNullOrEmpty(item) == false)
                    {
                        string fileName = "";
                        try
                        {
                            string imgURL = item.TrimStart('/');
                            imgURL = imgURL.Replace("/", "\\");
                            string oldImgPath = serverPath + imgURL;//原始图片物理地址
                            fileName = System.IO.Path.GetFileName(imgURL);//文件名扩展名
                            string suoLuePhotoPath = serverPath + "SuoLuePhoto"; //生成的缩略图文件夹
                            if (System.IO.Directory.Exists(suoLuePhotoPath) == false)
                            {
                                System.IO.Directory.CreateDirectory(suoLuePhotoPath);
                            }
                            string sltImgPath = suoLuePhotoPath + "\\" + fileName;//缩略图物理地址
                            ImgChuLi.SendSmallImage(oldImgPath, sltImgPath, 240, 240, 75, "CUT");
                        }
                        catch (Exception ex)
                        {
                            string saveFolder = "Log\\BLL";
                            string msg = "GrowBLL/AddGrowImgList，生成缩略图出错,文件名：" + fileName + "," + ex.Message;
                            Common.LogManager.AddLog(msg, saveFolder);
                        }
                    }
                    sqlCmdList.Add(arr);
                }
            }
            int efCount = SQLHepler.ExecSqlTransacation(sqlCmdList);
            return efCount;
        }

        /// <summary>
        /// 获取同一个学校的成长档案
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns></returns>
        public object GetGrowList(string currentUserId, string schoolId, int pageIndex, int pageSize, string serverAddress)
        {
            schoolId = Common.SqlFilter.Filter(schoolId);
            string sqlWhere = "[schoolId]='" + schoolId + "'";
            DataTable dt = SQLHepler.GetPaging("id", "[creatTime] desc", "[dbo].[T_Grow]", sqlWhere, pageIndex, pageSize);
            List<Grow> growList = new List<Grow>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    Grow model = new Grow();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    model.userId = item["userId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["userId"]);
                    model.words = item["words"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["words"]);
                    string creatTime_txt = item["creatTime"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["creatTime"]);
                    DateTime dd1 = DateTime.Now.Date;//当前时间
                    DateTime dd2 = Convert.ToDateTime(creatTime_txt).Date;
                    string time = Convert.ToDateTime(creatTime_txt).ToString("HH:mm");
                    TimeSpan span = dd1.Subtract(dd2);
                    double hours = span.TotalHours;//相差的小时数
                    string ddTT = "";
                    if (hours == 24)
                    {
                        ddTT = "昨天" + time;
                    }
                    else if (hours == 48)
                    {
                        ddTT = "前天" + time; ;
                    }
                    else if (hours == 0)
                    {
                        ddTT = "今天" + time;
                    }
                    else
                    {
                        ddTT = Convert.ToDateTime(creatTime_txt).ToString("yyyy-MM-dd HH:mm");
                    }
                    model.creatTime = ddTT;
                    model.status = item["status"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["status"]);
                    model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    model.userHeadImgURL = "img/psb.jpg";//替代图片
                    //查询用户属于哪个宝宝的家长
                    string sqlBaobao = "SELECT [stuName],j.jzName,j.guanxi,j.headImgURL FROM [dbo].[T_Student] as s left join [dbo].[T_JiaZhang] as j on j.stuId=s.studentId where [studentId]=(SELECT [stuId] FROM [dbo].[T_JiaZhang] where [jzId]='" + model.userId + "') and j.jzId='" + model.userId + "'";
                    DataTable dtBaoBao = SQLHepler.GetDataSet(sqlBaobao).Tables[0];
                    if (dtBaoBao != null && dtBaoBao.Rows.Count > 0)
                    {
                        string baobaoName = dtBaoBao.Rows[0]["stuName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dtBaoBao.Rows[0]["stuName"]);//宝宝姓名
                        string guanxi = dtBaoBao.Rows[0]["guanxi"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dtBaoBao.Rows[0]["guanxi"]);//宝宝对该家长的称呼，比如爸爸或妈妈或爷爷
                        string jzName = dtBaoBao.Rows[0]["jzName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dtBaoBao.Rows[0]["jzName"]);//家长姓名
                        model.userTxt = baobaoName + "" + guanxi;
                        if (dtBaoBao.Rows[0]["headImgURL"].GetType() != typeof(System.DBNull))
                        {
                            model.userHeadImgURL = serverAddress + Convert.ToString(dtBaoBao.Rows[0]["headImgURL"]);
                        }
                        else
                        {
                            model.userHeadImgURL = "img/psb.jpg";
                        }
                    }
                    //查询点赞的人列表
                    string[] zanArray = GetZanRenList(model.id, currentUserId);
                    model.zanValue = zanArray[1];  //当前登陆用户是否点过赞
                    model.zanRenListTxt = zanArray[0];
                    model.zanCount = zanArray[2];//点赞人数

                    //查询原始图片列表，以便获取缩略图           
                    List<object> suoLuePhotoList = new List<object>();//最多长度2,子集合最多装3个总共6个图片
                    List<string> imgList = new List<string>();//缩略图地址列表
                    string sql = "SELECT [id],[masterId],[imgURL],[creatTime] FROM [dbo].[T_growImgList] where [masterId]='" + model.id + "'";
                    DataTable imgListData = SQLHepler.GetDataSet(sql).Tables[0];
                    if (imgListData != null && imgListData.Rows.Count > 0)
                    {
                        for (int imgIndex = 0; imgIndex < imgListData.Rows.Count; imgIndex++)
                        {
                            var imgItem = imgListData.Rows[imgIndex];
                            //GrowImg img = new GrowImg();
                            //img.imgURL = imgItem["imgURL"] == typeof(System.DBNull) ? "" : Convert.ToString(imgItem["imgURL"]);//原始图片地址
                            string yanshiPhotoURL = imgItem["imgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(imgItem["imgURL"]);//原始图片地址
                            if (string.IsNullOrEmpty(yanshiPhotoURL) == false)
                            {
                                string fileName = System.IO.Path.GetFileName(yanshiPhotoURL);
                                string imgRequestURL = serverAddress + "/SuoLuePhoto/" + fileName;//缩略图网址
                                //img.imgURL = imgRequestURL;
                                imgList.Add(imgRequestURL);
                            }
                            //imgList.Add(img);
                        }
                        List<object> tempImgList1 = new List<object>();
                        List<object> tempImgList2 = new List<object>();
                        //将缩略图分为两组装入suoLuePhotoList，每组最多三个
                        for (int jj = 0; jj < imgList.Count; jj++)
                        {
                            var imgItem = imgList[jj];
                            if (jj < 3)//第一组
                            {
                                tempImgList1.Add(imgItem);
                            }
                            else//第二组
                            {
                                tempImgList2.Add(imgItem);
                            }
                        }
                        if (tempImgList1.Count > 0)
                        {
                            suoLuePhotoList.Add(tempImgList1);
                        }
                        if (tempImgList2.Count > 0)
                        {
                            suoLuePhotoList.Add(tempImgList2);
                        }
                        model.imgList = suoLuePhotoList;
                    }
                    //查询评价列表
                    List<PingLunDangAn> pinglunList = GetPingLunList(model.id);
                    model.pingjiaList = pinglunList;

                    growList.Add(model);
                }
            }
            return growList;
        }

        /// <summary>
        /// 查询同一个班级家长发表的成长档案，和管理改班级老师发布的成长档案
        /// </summary>
        /// <param name="currentUserId"></param>
        /// <param name="classId">当前学生所在班级</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="serverAddress"></param>
        /// <param name="teacherServerAddress">教师app服务地址</param>
        /// <returns></returns>
        public object GetGrowList2(string currentUserId, string classId, int pageIndex, int pageSize, string serverAddress, string teacherServerAddress)
        {
            SqlParameter[] parames = {
                new SqlParameter("@classId",classId),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@pageSize",pageSize)
            };
            DataTable dt = SQLHepler.ExecStoredProcedure("[p_jz_growlist]", parames);
            List<Grow> growList = new List<Grow>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    Grow model = new Grow();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    model.userId = item["userId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["userId"]);
                    model.words = item["words"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["words"]);
                    model.isShowDel = Convert.ToString(item["userId"]).Equals(currentUserId) ? true : false;//是否显示删除按钮
                    string videoImgUrl = item["videoImgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["videoImgURL"]);
                    if (!string.IsNullOrEmpty(videoImgUrl) && !string.IsNullOrWhiteSpace(videoImgUrl))
                    {
                        if (videoImgUrl.IndexOf("teacher") != -1)
                        {
                            model.videoImgURL = teacherServerAddress + videoImgUrl;
                        }
                        else
                        {
                            model.videoImgURL = serverAddress + videoImgUrl;
                        }
                    }
                    string videoURL = item["videoURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["videoURL"]);//小视频
                    if (!string.IsNullOrEmpty(videoURL) && !string.IsNullOrWhiteSpace(videoURL))
                    {
                        if (videoURL.IndexOf("teacher") != -1)
                        {
                            //老师发布的小视频
                            model.videoURL = teacherServerAddress + videoURL;
                        }
                        else
                        {
                            //家长发布的小视频
                            model.videoURL = serverAddress + videoURL;
                        }
                        model.dType = 2;
                    }
                    else
                    {
                        model.dType = 1;
                    }
                    string creatTime_js = item["creatTime"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["creatTime"]);
                    DateTime dd1 = DateTime.Now.Date;//当前时间
                    DateTime dd2 = Convert.ToDateTime(creatTime_js).Date;
                    string time = Convert.ToDateTime(creatTime_js).ToString("HH:mm");
                    TimeSpan span = dd1.Subtract(dd2);
                    double hours = span.TotalHours;//相差的小时数
                    string ddTT = "";
                    if (hours == 24)
                    {
                        ddTT = "昨天" + time;
                    }
                    else if (hours == 48)
                    {
                        ddTT = "前天" + time; ;
                    }
                    else if (hours == 0)
                    {
                        ddTT = "今天" + time; ;
                    }
                    else
                    {
                        ddTT = Convert.ToDateTime(creatTime_js).ToString("yyyy-MM-dd HH:mm");
                    }
                    model.creatTime = ddTT;
                    model.status = item["status"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["status"]);
                    model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                    model.userHeadImgURL = "img/psb.jpg";//替代图片
                    //查询用户属于哪个宝宝的家长
                    string sqlBaobao = "SELECT [stuName],j.jzName,j.guanxi,j.headImgURL FROM [dbo].[T_Student] as s inner join [dbo].[T_JiaZhang] as j on j.stuId=s.studentId where j.jzId='" + model.userId + "'";
                    DataTable dtBaoBao = SQLHepler.GetDataSet(sqlBaobao).Tables[0];
                    if (dtBaoBao != null && dtBaoBao.Rows.Count > 0)
                    {
                        string baobaoName = dtBaoBao.Rows[0]["stuName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dtBaoBao.Rows[0]["stuName"]);//宝宝姓名
                        string guanxi = dtBaoBao.Rows[0]["guanxi"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dtBaoBao.Rows[0]["guanxi"]);//宝宝对该家长的称呼，比如爸爸或妈妈或爷爷
                        string jzName = dtBaoBao.Rows[0]["jzName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(dtBaoBao.Rows[0]["jzName"]);//家长姓名
                        model.userTxt = baobaoName + "" + guanxi;
                        if (dtBaoBao.Rows[0]["headImgURL"].GetType() != typeof(System.DBNull))
                        {
                            model.userHeadImgURL = serverAddress + Convert.ToString(dtBaoBao.Rows[0]["headImgURL"]);
                        }
                        else
                        {
                            model.userHeadImgURL = "img/psb.jpg";
                        }
                    }
                    else
                    {
                        //说明是老师发布的
                        Teacher teachermodel = new SchoolBLL().GetTeacher(model.userId);
                        if (teachermodel != null)
                        {
                            if (string.IsNullOrEmpty(teachermodel.headImgURL) == false && string.IsNullOrWhiteSpace(teachermodel.headImgURL) == false)
                            {
                                string headurl = System.IO.Path.GetFileNameWithoutExtension(teachermodel.headImgURL);
                                string headurl2 = teachermodel.headImgURL.Replace(headurl, "small_" + headurl);
                                model.userHeadImgURL = teacherServerAddress + headurl2;
                            }
                            model.userTxt = teachermodel.teacherName + "老师";
                        }
                    }
                    //查询点赞的人列表
                    string[] zanArray = GetZanRenList(model.id, currentUserId);
                    model.zanValue = zanArray[1];  //当前登陆用户是否点过赞
                    model.zanRenListTxt = zanArray[0];
                    model.zanCount = zanArray[2];//点赞人数

                    //查询原始图片列表，以便获取缩略图           
                    List<object> suoLuePhotoList = new List<object>();//最多长度2,子集合最多装3个总共6个图片
                    List<string> imgList = new List<string>();//存放的是原始图片路径集合
                    string sql = "SELECT [id],[masterId],[imgURL],[creatTime] FROM [dbo].[T_growImgList] where [masterId]='" + model.id + "'";
                    DataTable imgListData = SQLHepler.GetDataSet(sql).Tables[0];
                    if (imgListData != null && imgListData.Rows.Count > 0)
                    {
                        for (int imgIndex = 0; imgIndex < imgListData.Rows.Count; imgIndex++)
                        {
                            var imgItem = imgListData.Rows[imgIndex];
                            //GrowImg img = new GrowImg();
                            //img.imgURL = imgItem["imgURL"] == typeof(System.DBNull) ? "" : Convert.ToString(imgItem["imgURL"]);//原始图片地址
                            string yanshiPhotoURL = imgItem["imgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(imgItem["imgURL"]);//原始图片地址
                            if (string.IsNullOrEmpty(yanshiPhotoURL) == false)
                            {
                                string fileName = System.IO.Path.GetFileName(yanshiPhotoURL);//文件名和扩展名
                                string imgRequestURL = string.Empty;//缩略图网址
                                if (yanshiPhotoURL.IndexOf("teacher") != -1)
                                {
                                    //老师发布图片
                                    string imgFileName = yanshiPhotoURL.Replace(fileName, "small_" + fileName);
                                    imgRequestURL = teacherServerAddress + imgFileName;//缩略图地址
                                    //string originalImgURL = teacherServerAddress + yanshiPhotoURL;//原始图地址
                                    //imgList.Add(originalImgURL);//
                                }
                                else
                                {
                                    imgRequestURL = serverAddress + "/SuoLuePhoto/" + fileName;
                                }
                                imgList.Add(imgRequestURL);
                            }
                        }
                        List<object> tempImgList1 = new List<object>();
                        List<object> tempImgList2 = new List<object>();
                        //将缩略图分为两组装入suoLuePhotoList，每组最多三个
                        for (int jj = 0; jj < imgList.Count; jj++)
                        {
                            var imgItem = imgList[jj];
                            if (jj < 3)//第一组
                            {
                                tempImgList1.Add(imgItem);
                            }
                            else//第二组
                            {
                                tempImgList2.Add(imgItem);
                            }
                        }
                        if (tempImgList1.Count > 0)
                        {
                            suoLuePhotoList.Add(tempImgList1);
                        }
                        if (tempImgList2.Count > 0)
                        {
                            suoLuePhotoList.Add(tempImgList2);
                        }
                        model.imgList = suoLuePhotoList;//存放的是原始图片路径集合
                    }
                    //查询评价列表
                    List<PingLunDangAn> pinglunList = GetPingLunList(model.id);
                    model.pingjiaList = pinglunList;

                    growList.Add(model);
                }
            }
            return growList;
        }

        /// <summary>
        /// 点赞
        /// </summary>
        /// <param name="zanGrowId">被赞的成长档案记录id</param>
        /// <param name="zanUserId">点击的用户id</param>
        /// <returns></returns>
        public int AddZan(string zanGrowId, string zanUserId)
        {
            string sql = "INSERT INTO [dbo].[T_Zan]([id],[zanGrowId],[zanUserId]) VALUES(@id,@zanGrowId,@zanUserId)";
            SqlParameter[] parmes =
            {
                new SqlParameter("@id",Guid.NewGuid().ToString()),
                new SqlParameter("@zanGrowId",zanGrowId),
                new SqlParameter("@zanUserId",zanUserId)
            };
            int count = SQLHepler.ExecuteSql(sql, parmes);
            return count;
        }

        /// <summary>
        /// 查询点赞的人姓名，以及总的点赞数
        /// </summary>
        /// <param name="growId"></param>
        /// <returns></returns>
        public string[] GetZanRenList(string growId, string currentUserId)
        {
            List<Zan> list = new List<Zan>();
            string sql1 = " SELECT top 5 m.[id],[zanGrowId],[zanUserId],jz.[jzName],jz.[guanxi],s.[stuName],t.teacherName FROM [dbo].[T_Zan] as m left join [dbo].[T_JiaZhang] as jz on jz.jzId=m.[zanUserId] left join [dbo].T_Student as s on s.studentId=jz.[stuId] left join [dbo].[T_Teacher] as t on t.id=m.[zanUserId] where [zanGrowId]=@zanGrowId";
            string sql2 = " SELECT count(1) FROM [dbo].[T_Zan] where [zanGrowId]=@zanGrowId ";
            DataSet ds = SQLHepler.GetDataSet(sql1 + sql2, new SqlParameter("@zanGrowId", growId));
            int count = 0;//点赞的人数
            string zanString = "";
            string[] zanArr = new string[3];//下标0=点赞描述字符串;下标1=是否存在当前账户，1表示存在，0表示没有；下标2表示存的点赞人数
            zanArr[1] = "0";
            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var item = ds.Tables[0].Rows[i];
                    Zan model = new Zan();
                    model.zanUserId = item["zanUserId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["zanUserId"]);
                    string baobaoName = item["stuName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["stuName"]);//宝宝姓名
                    string guanxi = item["guanxi"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["guanxi"]);//宝宝对家长的称呼
                    string teacherName = item["teacherName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["teacherName"]);//点赞的老师
                    model.userName = baobaoName + "" + guanxi;//点赞人名称：宝宝姓名+称呼
                    if (string.IsNullOrEmpty(teacherName) == false)
                    {
                        model.userName = teacherName + "老师";
                    }
                    list.Add(model);
                }
            }
            if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
            {
                count = ds.Tables[1].Rows[0][0].GetType() == typeof(System.DBNull) ? 0 : Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            }
            StringBuilder zanTxt = new StringBuilder();
            if (list != null)
            {
                if (list.Count > 5)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        var item = list[i];
                        if (item.zanUserId.Equals(currentUserId))
                        {
                            //zanTxt.Append("我、");
                            zanArr[1] = "1";
                        }
                        //else
                        //{
                        //    zanTxt.Append(item.userName + "、");
                        //}
                        zanTxt.Append(item.userName + "、");
                    }
                    zanString = zanTxt.ToString().TrimEnd('、');
                }
                else
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        var item = list[i];
                        if (item.zanUserId.Equals(currentUserId))
                        {
                            //zanTxt.Append("我、");
                            zanArr[1] = "1";
                        }
                        //else
                        //{
                        //    zanTxt.Append(item.userName + "、");
                        //}
                        zanTxt.Append(item.userName + "、");
                    }
                    zanString = zanTxt.ToString().TrimEnd('、');
                }
            }
            zanArr[0] = zanString;
            zanArr[2] = count + "";//赞的人数
            return zanArr;
        }

        /// <summary>
        /// 保存评论
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="growId">说说主键id</param>
        /// <param name="word">评论内容</param>
        /// <returns></returns>
        public int AddPingLun(string userId, string growId, string word)
        {
            string sql = "INSERT INTO [dbo].[T_PingLunDangAn]([id],[userId],[growId],[creatTime],[words]) VALUES(@id,@userId,@growId,@creatTime,@words)";
            SqlParameter[] parames =
            {
                new SqlParameter("@id",Guid.NewGuid().ToString()),
                new SqlParameter("@userId",userId),
                new SqlParameter("@growId",growId),
                new SqlParameter("@creatTime",DateTime.Now),
                new SqlParameter("@words",word)
            };
            int count = SQLHepler.ExecuteSql(sql, parames);
            return count;
        }

        /// <summary>
        /// 根据成长档案记录主键，查询该记录的评论
        /// </summary>
        /// <param name="growId"></param>
        /// <returns></returns>
        public List<PingLunDangAn> GetPingLunList(string growId)
        {
            string sql = "SELECT m.[id],[userId],[growId],[creatTime],[words],jz.[jzName],s.stuName,t.teacherName,jz.guanxi FROM [dbo].[T_PingLunDangAn] as m left join [dbo].[T_JiaZhang] as jz on jz.[jzId]=m.userId left join [dbo].[T_Student] as s on s.studentId=jz.[stuId] left join [dbo].[T_Teacher] as t on t.id=m.userId where [growId]=@growId order by [creatTime] asc";
            DataTable dt = SQLHepler.GetDataSet(sql, new SqlParameter("@growId", growId)).Tables[0];
            List<PingLunDangAn> list = new List<PingLunDangAn>();
            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    PingLunDangAn model = new PingLunDangAn();
                    model.id = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    model.userId = item["userId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["userId"]);
                    model.growId = item["growId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["growId"]);
                    model.creatTime = item["creatTime"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["creatTime"]);
                    model.words = item["words"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["words"]);
                    string bbName = item["stuName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["stuName"]);//宝宝姓名
                    string teacherName = item["teacherName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["teacherName"]);//教师姓名
                    string guanxi = item["guanxi"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["guanxi"]);//关系
                    if (!string.IsNullOrEmpty(teacherName))
                    {
                        model.pjName = teacherName + "老师";//评价人姓名：老师姓名
                    }
                    else
                    {
                        model.pjName = bbName + "" + guanxi;//评价人姓名：宝宝姓名+关系
                    }
                    if (string.IsNullOrEmpty(model.creatTime) == false)
                    {
                        string timeTxt = Convert.ToDateTime(model.creatTime).ToString("HH:mm");
                        DateTime time = Convert.ToDateTime(model.creatTime).Date;
                        DateTime nowDate = DateTime.Now.Date;
                        TimeSpan span = nowDate.Subtract(time);
                        if (span.TotalHours == 0)
                        {
                            model.creatTime = "今天" + timeTxt;
                        }
                        else if (span.TotalHours == 24)
                        {
                            model.creatTime = "昨天" + timeTxt;
                        }
                        else if (span.TotalHours == 48)
                        {
                            model.creatTime = "前天" + timeTxt;
                        }
                        else
                        {
                            model.creatTime = Convert.ToDateTime(model.creatTime).ToString("yyyy-MM-dd HH:mm");
                        }
                    }
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 保存成长档案发表的小视频
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="word"></param>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="videoURL"></param>
        /// <param name="videoImgURL"></param>
        /// <returns></returns>
        public int AddGrowVideo(string userId, string word, string schoolId, string classId, string videoURL, string videoImgURL)
        {
            List<SqlParameter> paramelist = new List<SqlParameter>();
            paramelist.Add(new SqlParameter("@userId", userId));
            paramelist.Add(new SqlParameter("@status", 1));
            paramelist.Add(new SqlParameter("@schoolId", schoolId));
            paramelist.Add(new SqlParameter("@classId", classId));
            paramelist.Add(new SqlParameter("@videoURL", videoURL));
            string words_sql = "";
            string wordsParame = "";
            if (!string.IsNullOrEmpty(word) && !string.IsNullOrWhiteSpace(word))
            {
                words_sql = ",[words]";
                wordsParame = ",@words";
                paramelist.Add(new SqlParameter("@words", word));
            }
            string videoImgURL_sql = "";
            string videoImgURLParame = "";
            if (!string.IsNullOrEmpty(videoImgURL) && !string.IsNullOrWhiteSpace(videoImgURL))
            {
                videoImgURL_sql = ",[videoImgURL]";
                videoImgURLParame = ",@videoImgURL";
                paramelist.Add(new SqlParameter("@videoImgURL", videoImgURL));
            }
            string sql = "INSERT INTO [dbo].[T_Grow]([id],[userId]" + words_sql + ",[creatTime],[status],[schoolId],[classId],[videoURL]" + videoImgURL_sql + ") VALUES(NEWID(),@userId" + wordsParame + ",GETDATE(),@status,@schoolId,@classId,@videoURL" + videoImgURLParame + ")";
            SqlParameter[] parames = paramelist.ToArray();
            int count = SQLHepler.ExecuteSql(sql, parames);
            return 1;
        }

        /// <summary>
        /// 根据主键查询主表记录
        /// </summary>
        /// <param name="growId"></param>
        /// <returns></returns>
        public Grow GetGrowById(string growId)
        {
            string sql = "SELECT [id],[userId],[words],[creatTime],[status],[schoolId],[classId],[videoURL],[videoImgURL] FROM [dbo].[T_Grow] where [id]=@growId";
            DataTable dt = SQLHepler.GetDataSet(sql, new SqlParameter("@growId", growId)).Tables[0];
            Grow model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                model = new Grow();
                var record = dt.Rows[0];
                model.id = record["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["id"]);
                model.userId = record["userId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["userId"]);
                model.words = record["words"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["words"]);
                model.creatTime = record["creatTime"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["creatTime"]);
                model.status = record["status"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["status"]);
                model.schoolId = record["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["schoolId"]);
                model.classId = record["classId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["classId"]);
                model.videoURL = record["videoURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["videoURL"]);
                model.videoImgURL = record["videoImgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(record["videoImgURL"]);
            }
            return model;
        }


        /// <summary>
        /// 删除成长档案一条记录
        /// </summary>
        /// <param name="growId">grow表主键</param>
        /// <returns></returns>
        public int DelGrowById(string growId, out Grow model)
        {
            //查询主表记录
            model = GetGrowById(growId);
            List<object[]> cmdList = new List<object[]>();
            string sql_1 = "delete from [dbo].[T_Zan] where [zanGrowId]=@growId";
            SqlParameter[] parames1 = { new SqlParameter("@growId", growId) };
            object[] arr1 = {
               sql_1,parames1
            };

            string sql_2 = "delete from [dbo].[T_PingLunDangAn] where [growId]=@growId";
            SqlParameter[] parames2 = { new SqlParameter("@growId", growId) };
            object[] arr2 = {
               sql_2,parames2
            };

            string sql_3 = "delete from [dbo].[T_growImgList] where [masterId]=@growId";
            SqlParameter[] parames3 = { new SqlParameter("@growId", growId) };
            object[] arr3 = {
               sql_3,parames3
            };

            string sql_4 = "delete from [dbo].[T_Grow] where [id]=@growId";
            SqlParameter[] parames4 = { new SqlParameter("@growId", growId) };
            object[] arr4 = {
               sql_4,parames4
            };
            cmdList.Add(arr1);
            cmdList.Add(arr2);
            cmdList.Add(arr3);
            cmdList.Add(arr4);
            int count = SQLHepler.ExecSqlTransacation(cmdList);
            return count;
        }
    }
}
